# coding: utf-8
"""
    Define MySQL Database Model
"""

import web
from time import time
from sqlalchemy import create_engine
from sqlalchemy import Column, Table, MetaData, ForeignKey
from sqlalchemy.databases import mysql
from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from helper import ip2num, num2ip

engine = create_engine('mysql://root:root@localhost/test?charset=utf8', echo=True)

Session = scoped_session(sessionmaker(bind=engine))

def load_sqlalchemy(hander):
    web.ctx.orm = scoped_session(sessionmaker(bind = engine))
    try:
        return hander()
    except web.HTTPError:
        web.ctx.orm.commit()
        raise
    except:
        web.ctx.orm.rollback()
        raise
    finally:
        web.ctx.orm.commit()

Base = declarative_base()

# 用户对商店收藏表 favorite
# 用户对商品收藏表??? 如果商品过期?

# Users and Roles Relationship Table.
tb_user_roles = Table('user_roles', Base.metadata,
    Column('user_id', mysql.INTEGER(11, unsigned=True), ForeignKey('users.id')),
    Column('role_id', mysql.INTEGER(11, unsigned=True), ForeignKey('roles.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 用户 <=> 店铺等级对应表
tb_user_levels = Table('user_levels', Base.metadata,
    Column('user_id', mysql.INTEGER(11, unsigned=True), ForeignKey('users.id')),
    Column('level_id', mysql.INTEGER(11, unsigned=True), ForeignKey('levels.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 商店 <=> 种类对应表
tb_shop_types = Table('shop_types', Base.metadata,
    Column('shop_id', mysql.INTEGER(11, unsigned=True), ForeignKey('shops.id')),
    Column('type_id', mysql.INTEGER(11, unsigned=True), ForeignKey('types.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 商店 <=> 货物对应表
tb_shop_products = Table('shop_products', Base.metadata,
    Column('shop_id', mysql.INTEGER(11, unsigned=True), ForeignKey('shops.id')),
    Column('product_id', mysql.INTEGER(11, unsigned=True), ForeignKey('products.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 商店 <=> 物品分类表
tb_shop_species = Table('shop_species', Base.metadata,
    Column('shop_id', mysql.INTEGER(11, unsigned=True), ForeignKey('shops.id')),
    Column('specie_id', mysql.INTEGER(11, unsigned=True), ForeignKey('species.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 商店 <=> 会员等级对应表
tb_shop_levels = Table('shop_levels', Base.metadata,
    Column('shop_id', mysql.INTEGER(11, unsigned=True), ForeignKey('shops.id')),
    Column('level_id', mysql.INTEGER(11, unsigned=True), ForeignKey('levels.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 商店分类 <=> 商店
tb_type_shops = Table('type_shops', Base.metadata,
    Column('type_id', mysql.INTEGER(11, unsigned=True), ForeignKey('types.id')),
    Column('shop_id', mysql.INTEGER(11, unsigned=True), ForeignKey('shops.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 物品 <=> 物品分类表
tb_product_species = Table('product_species', Base.metadata,
    Column('product_id', mysql.INTEGER(11, unsigned=True), ForeignKey('products.id')),
    Column('specie_id', mysql.INTEGER(11, unsigned=True), ForeignKey('species.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 物品 <=> 评价表
tb_product_evaluations = Table('product_evaluations', Base.metadata,
    Column('product_id', mysql.INTEGER(11, unsigned=True), ForeignKey('products.id')),
    Column('evaluation_id', mysql.INTEGER(11, unsigned=True), ForeignKey('evaluations.id')),
    mysql_engine        = 'myisam',
    mysql_charset       = 'utf8',
)

# 用户表, 用户登录使用
class User(Base):
    """
        Define Table: users
    """
    __tablename__ = 'users'
    __table_args__ = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id              = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)        
    username        = Column(mysql.CHAR(32), nullable=False, unique=True)       # 用户登录名
    password        = Column(mysql.CHAR(64), nullable=False)        # 登录密码
    email           = Column(mysql.VARCHAR(127), nullable=False, unique=True)       # 电子邮件
    logins          = Column(mysql.INTEGER(10, unsigned=True), nullable=False, default=0)       # 登录时间
    ip              = Column(mysql.INTEGER(10, unsigned=True), nullable=False, default=0)       # 登录IP
    created         = Column(mysql.INTEGER(10, unsigned=True), nullable=False, index=True)      # 创建时间
    
    # many to many user_roles<->users
    roles           = relationship('Role', secondary=tb_user_roles, backref='users')        # 用户角色
    
    # one to many 
    evaluation      = relationship('Evaluation', order_by='Evaluation.id', backref='users')     # 评价记录
    order           = relationship('Order', order_by='Order.id', backref='users')       # 订单记录
    transaction     = relationship('Transaction', order_by='Transaction.id', backref='users')       # 交易记录
    
    def __init__(self, username, password, email, created = False):
        self.username   = username
        self.password   = password
        self.email      = email
        self.logins     = int(time())
        self.ip         = ip2num(web.ctx.ip)
        self.created    = created and time()
    
    def __repr__(self):
        return "<User('%s', '%s', '%s', '%s', '%s', '%s', '%s')>" % \
                (self.id, self.username, self.password, self.email, self.logins, num2ip(self.ip), self.created)

# 权限表, 定义网站用户等级
class Role(Base):
    """
        Define Table: roles
    """
    __tablename__ = 'roles'
    __table_args__ = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id              = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)        
    role            = Column(mysql.CHAR(32), nullable=False, unique=True)       # 权限名
    description     = Column(mysql.TINYTEXT(), nullable=False, default='')      # 权限描述
    
    def __init__(self, role, description):
        self.role   = role
        self.description    = description
        
    def __repr__(self):
        return "<Role('%s', '%s', '%s')>" % \
                (self.id, self.role, self.description)


# 名片卡(待改进)
class Postcard(Base):
    __tablename__   = 'postcards'
    __table_args__  = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id              = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    postcard        = Column(mysql.VARCHAR(127), nullable=False, index=True)
    created         = Column(mysql.INTEGER(10, unsigned=True), index=True)
    
    # one to many
    postcard_info   = relationship('Postcard_info', order_by='Postcard_info.id', backref='postcards')
    
    def __init__(self, postcard, created):
        self.postcard       = postcard
        self.created        = created
    
    def __repr__(self):
        return "<Postcard('%s', '%s')>" % (self.postcard, self.created)

class Postcard_info(Base):
    __tablename__           = 'postcard_infos'
    __table_args__          = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id              = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    postcard_id     = Column(mysql.INTEGER(11, unsigned=True), ForeignKey('postcards.id'))
    name            = Column(mysql.VARCHAR(63), nullable=False, index=True)
    value           = Column(mysql.TINYTEXT, nullable=False)
    
    # many to one
    postcard        = relationship('Postcard', backref=backref('postcard_infos', order_by=id))
    
    def __init__(self, name, value):
        self.name           = name
        self.value          = value
    
    def __repr__(self):
        return "Postcard_info<'%s', '%s'>" % (self.name, self.value)

# 店铺表
class Shop(Base):
    __tablename__       = 'shops'
    __table_args__      = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id              = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    shop            = Column(mysql.VARCHAR(127), nullable=False, index=True)    # 店铺名
    introduction    = Column(mysql.TEXT, nullable=False)        # 店铺介绍
    telephone       = Column(mysql.TEXT, nullable=False)        # 联系电话
    address         = Column(mysql.TEXT, nullable=False)        # 联系地址
    created         = Column(mysql.INTEGER(10, unsigned=True), nullable=True)       # 创建时间
    
    # many to many
    product         = relationship('Product', secondary=tb_shop_products, backref='shops')      # 商品信息表
    specie          = relationship('Specie', secondary=tb_shop_species, backref='shops')        # 商铺商品分类表        
    level           = relationship('Level', secondary=tb_shop_levels, backref='shops')      # 商铺会员等级表
    
    # one to many
    transaction     = relationship('Transaction', order_by='Transaction.id', backref='shops')       # 交易记录表

    def __init__(self, shop, introduction, telephone, address, created):
        self.shop           = shop
        self.introduction   = introduction
        self.telephone      = telephone
        self.address        = address
        self.created        = created
    
    def __repr__(self):
        return "<Shop('%s', '%s', '%s', '%s', '%s', '%s')>" % \
                (self.id, self.shop, self.introduction, self.telephone, self.address, self.created)

# 店铺会员等级定义
class Level(Base):
    __tablename__       = 'levels'
    __table_args__      = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id                  = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    level               = Column(mysql.VARCHAR(63), nullable=False)     # 等级名
    caption             = Column(mysql.TINYTEXT)        # 等级描述

    # many to many 
    user                = relationship('User', secondary=tb_user_levels, backref='Levels')      # 用户关联
    
    def __init__(self, level, caption):
        self.level          = level
        self.caption        = caption
        
    def __repr__(self):
        return "<Level('%s', '%s', '%s')>" % (self.id, self.level, self.caption)

# 产品信息表
class Product(Base):
    __tablename__       = 'products'
    __table_args__      = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id              = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    product         = Column(mysql.VARCHAR(127), nullable=False, index=True)        # 产品名称
    summary         = Column(mysql.VARCHAR(255), nullable=False, index=True)        # 产品摘要
    description     = Column(mysql.TEXT, nullable=False)        # 产品描述
    created         = Column(mysql.INTEGER(10, unsigned=True), nullable=False)      # 创建时间
    
    # many to many
    specie          = relationship('Product', secondary=tb_product_species, backref='products')     # 对应分类
    
    # one to many
    transaction     = relationship('Transaction', order_by='Transaction.id', backref='products')    # 交易记录
    
    def __init__(self, product, summary, description, created):
        self.product        = product
        self.summary        = summary
        self.description    = description
        self.created        = created
    
    def __repr__(self):
        return "<Product('%s', '%s', '%s', '%s', '%s')>" % \
                (self.id, self.product, self.summary, self.description, self.created)

# 评价表
class Evaluation(Base):
    __tablename__       = 'evaluations'
    __table_args__      = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id                  = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    user_id             = Column(mysql.INTEGER(11, unsigned=True), ForeignKey('users.id'))      # 用户ID
    evaluation          = Column(mysql.TEXT, nullable=False)        # 评价内容
    created             = Column(mysql.INTEGER(10, unsigned=True), nullable=False)      # 创建时间
    
    # many to one
    user                = relationship('User', backref=backref('evaluations', order_by=id))     # 对应用户记录
    
    def __init__(self, user_id, evaluation, created):
        self.user_id        = user_id
        self.evaluation     = evaluation
        self.created        = created

    def __repr__(self):
        return "<Product('%s', '%s', '%s')>" % (self.user_id, self.evaluation, self.created)

# 商店种类
# 无限分类, 父子分类
class Type(Base):
    __tablename__       = 'types'
    __table_args__      = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id                  = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    pid                 = Column(mysql.INTEGER(11, unsigned=True), nullable=False, default=0)       # 父ID
    type                = Column(mysql.VARCHAR(127), nullable=False)        # 类型名称
    caption             = Column(mysql.VARCHAR(255), nullable=False)        # 描述
    
    # many to many
    shop                = relationship('Shop', secondary=tb_type_shops, backref='types')        # 商店关联表
    
    def __init__(self, pid, type, caption):
        self.pid            = pid
        self.type           = type
        self.caption        = caption
    
    def __repr__(self):
        return "<Type('%s', '%s', '%s', '%s')>" % (self.id, self.pid, self.type, self.caption)

# 产品种类
class Specie(Base):
    __tablename__       = 'species'
    __table_args__      = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id                  = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    pid                 = Column(mysql.INTEGER(11, unsigned=True), nullable=False, default=0)       # 父ID
    specie              = Column(mysql.VARCHAR(127), nullable=False)        # 种类名
    caption             = Column(mysql.VARCHAR(255), nullable=False)        # 种类描述
    
    # many to many
    product             = relationship('Product', secondary=tb_product_species, backref='species')      # 产品对应表
    
    def __init__(self, pid, specie, caption):
        self.pid            = pid
        self.specie         = specie
        self.caption        = caption
    
    def __repr__(self):
        return "<Type('%s', '%s', '%s', '%s')>" % (self.id, self.pid, self.specie, self.caption)

#订单表, 订单用户对应表, 订单商店对应表, 订单, 交易记录, 1:m
class Order(Base):
    __tablename__       = 'orders'
    __table_args__      = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id                  = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    user_id             = Column(mysql.INTEGER(11, unsigned=True), ForeignKey('users.id'))      # 用户ID
    order               = Column(mysql.VARCHAR(255), nullable=False)        # 订单名
    remark              = Column(mysql.TINYTEXT)        # 订单备注
    client              = Column(mysql.VARCHAR(63), nullable=False)     # 客户姓名
    mobile              = Column(mysql.VARCHAR(31), nullable=False)     # 客户电话
    zipcode             = Column(mysql.VARCHAR(15), nullable=False, default=0)      # 邮政编码, 默认为空
    arrive              = Column(mysql.TINYTEXT, nullable=False)        # 客户地址
    created             = Column(mysql.INTEGER(10, unsigned=True), nullable=False, index=True)      # 创建时间
    
    # many to one 
    user                = relationship('User', backref=backref('orders', order_by=id))      # 用户关联表
    
    # one to many
    transaction         = relationship('Transaction', order_by='Transaction.id', backref='orders')      # 交易记录关联
    
    def __init__(self, order, remark, name, mobile, zipcode, arrive, created):
        self.order          = order
        self.remark         = remark
        self.name           = name
        self.mobile         = mobile
        self.zipcode        = zipcode
        self.arrive         = arrive
        self.created        = created

    def __repr__(self):
        return "<Order('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')>" % \
                (self.id, self.order, self.remark, self.name, self.mobile, self.zipcode, self.arrive, self.created)    

# 交易记录表, 交易记录用户对应表, 交易记录是商户对应表
class Transaction(Base):
    __tablename__       = 'transactions'
    __table_args__      = {'mysql_engine':'myisam', 'mysql_charset':'utf8'}
    
    id                  = Column(mysql.INTEGER(11, unsigned=True), primary_key=True, autoincrement=True)
    user_id             = Column(mysql.INTEGER(11, unsigned=True), ForeignKey('users.id'))      # 用户ID
    shop_id             = Column(mysql.INTEGER(11, unsigned=True), ForeignKey('shops.id'))      # 商铺ID
    order_id            = Column(mysql.INTEGER(11, unsigned=True), ForeignKey('orders.id'))     # 订单ID
    product_id          = Column(mysql.INTEGER(11, unsigned=True), ForeignKey('products.id'))   # 产品ID
    quantity            = Column(mysql.INTEGER(11, unsigned=True), nullable=False)      # 产品数量
    price               = Column(mysql.DECIMAL, nullable=False)     # 产品单价
    created             = Column(mysql.INTEGER(10, unsigned=True), nullable=False, index=True)      # 创建时间
    
    # many to one
    user                = relationship('User', backref=backref('transactions', order_by=id))        # 用户关联表
    shop                = relationship('Shop', backref=backref('transactions', order_by=id))        # 商铺关联表
    order               = relationship('Order', backref=backref('transactions', order_by=id))       # 订单关联表
    product             = relationship('Product', backref=backref('transactions', order_by=id))     # 产品关联表
    
    def __init__(self, quantity, price, created):
        self.quantity       = quantity
        self.price          = price
        self.created        = created
        
    def __repr__(self):
        return "<Transaction('%s', '%s', '%s', '%s')>" % \
                (self.id, self.quantity, self.price, self.created)

if __name__ == '__main__':
    """
    Base.metadata.create_all(engine)
    Session.add_all([
        Role('root', 'sys root'),
        Role('admin', 'sys admin'),
        Role('user', 'sys user'),
        Role('deny', 'sys deny'),
    ])
    Session.commit()
    """
    """ Relationship update
    role = Session.query(Role).filter(Role.role=='root').one()
    user = User('zukhz', '123', 'zukhzz@gmail.com', True)
    user.roles.append(role)
    Session.commit()
    """
    print web.ctx.ip
